package kr.hkit.summary_java.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;

import kr.hkit.myframework.db.ConnectionFactory;
import kr.hkit.myframework.db.DbUtil;
import kr.hkit.myframework.uicomp.InputCombo;
import kr.hkit.summary_java.erp.dto.Employee;

public class EmployeeDAO implements DaoInterface<Employee> {

	private Connection connection;

	public EmployeeDAO(Connection connection) {
		this.connection = connection;
	}

	@Override
	// 입력
	public void insertItem(Employee item) throws SQLException {
		String sql = "insert into employee values (?,?,?,?,?,?)";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getEmpNo());
		pstmt.setString(2, item.getEmpName());
		pstmt.setInt(3, item.getTitle());
		pstmt.setInt(4, item.getManager());
		pstmt.setInt(5, item.getSalary());
		pstmt.setInt(6, item.getDeptNo());
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
	}

	@Override
	// 삭제
	public void deleteItemByNo(Employee item) throws SQLException {
		String sql = "delete from employee where empno=?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getEmpNo());
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
	}

	@Override
	// 수정
	public void updateItemByNo(Employee item) throws SQLException {
		
		String sql = "update employee set empname=?, title=?, "
				+ "manager=?, salary=?, deptno=? "
				+ "where empno=?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setString(1, item.getEmpName());
		pstmt.setInt(2, item.getTitle()); 
		pstmt.setInt(3, item.getManager());
		pstmt.setInt(4, item.getSalary());
		pstmt.setInt(5, item.getDeptNo());//
		pstmt.setInt(6, item.getEmpNo());
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
	}
	


	@Override
	// 검색
	public Employee searchItem(Employee item) throws SQLException {
		Employee employee = null;
		String sql = "select empno,empname,title,manager,salary,deptno "
				+ "from employee where empno=?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getEmpNo());
		ResultSet rs = pstmt.executeQuery();

		if (rs.next()) {
			employee = new Employee();
			employee.setEmpNo(rs.getInt(1));
			employee.setEmpName(rs.getString(2));
			employee.setTitle(rs.getInt(3));
			employee.setManager(rs.getInt(4));
			employee.setSalary(rs.getInt(5));
			employee.setDeptNo(rs.getInt(6));
		} 
		
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return employee;
	}

	@Override
	public ArrayList<Employee> listItems() throws SQLException { //리스트보기

		ArrayList<Employee> list = new ArrayList<>();
		String sql = "select empno,empname,title,manager,salary,deptno from employee";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();

		while (rs.next()) {
			list.add(new Employee(rs.getInt(1), rs.getString(2), rs.getInt(3),
					rs.getInt(4), rs.getInt(5), rs.getInt(6)));
		}

		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return list;
	}

	// 리스트보기  - 직책명, 부서명
	public ArrayList<Employee> getDeptList() throws SQLException {

		ArrayList<Employee> listName = new ArrayList<>();

		String sql = "select e.empno, e.empname, t.titlename, e.manager, e.salary, d.deptname "
				+ "from employee e , department d, code_title t "
				+ "where e.deptno = d.deptno and e.title=t.titleno order by e.empno";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();

		while (rs.next()) {
			Employee e= new Employee(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getInt(5), rs.getString(6));
			listName.add(e);
		}

		DbUtil.close(rs);
		DbUtil.close(pstmt);

		return listName;
	}

	
	// 부서명 콤보박스 - 부서명
	public String[] getDeptItems() throws SQLException { 

		ArrayList<String> listDepts = new ArrayList<>();

		String sql = "select deptname from department";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		
		while (rs.next()) {
			listDepts.add(rs.getString("deptname"));
		}

		String[] arrDepts = new String[listDepts.size()];
		listDepts.toArray(arrDepts);

		DbUtil.close(rs);
		DbUtil.close(pstmt);

		return arrDepts;
	}
	
	 //상급자명 콤보박스  - 상급자명이름
	public String[] getManagerItems() throws SQLException {
		
		ArrayList<String> empManager = new ArrayList<>();
		String sql = "select empname from employee";
		PreparedStatement pstmt=connection.prepareStatement(sql);
		
		ResultSet rs = pstmt.executeQuery();
		
		while (rs.next()) {
			empManager.add(rs.getString("empname"));
		}
		
		String[] arrManager = new String[empManager.size()];
		empManager.toArray(arrManager);
		
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		
		return arrManager;
	}
	
	
	//직책명  콤보박스
	public String[] getTitleitems() throws SQLException {
		
		ArrayList<String> empTitle = new ArrayList<>();
		String sql = "select titlename from code_title";
		PreparedStatement pstmt=connection.prepareStatement(sql);
		
		ResultSet rs = pstmt.executeQuery();
		
		while (rs.next()) {
			empTitle.add(rs.getString("titlename"));
		}
		
		String[] arrTitle = new String[empTitle.size()];
		empTitle.toArray(arrTitle); 
		
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		
		return arrTitle;
	}
	
	
	//상급자명 콤보상자 : 번호로 이름
	public String selectedEmpNameByEmpNo(int empno) throws SQLException {
		String sql = "select empname from employee where empno = ?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, empno);
		ResultSet rs = pstmt.executeQuery();
		String empName = null;
		if(rs.next()) {
			empName = rs.getString("empname");
		}
		
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return empName;
		
	}
	
	//상급자명 콤보상자 : 이름으로 번호 
	public int selectedEmpNoByEmpName (String empname) throws SQLException {
		String sql = "select empno from employee where empname = ?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setString(1, empname);
		ResultSet rs = pstmt.executeQuery();
		int empNo = 0 ;
		
		if (rs.next()) {
			empNo = rs.getInt("empno");
		}
		
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		
		return empNo; 
		
	}
}


	/*//쌤꺼 따라함 
	public HashMap<String, Integer> selectedManager() throws SQLException{
		HashMap<String, Integer> hashList = new HashMap<String, Integer>();
		
		ArrayList<String> temp = new ArrayList<>();
		String sql = "select empno, titlename as title, empname , d.deptname as dept "
				+ "from employee e, code_title c, department d	"
				+ "where e.title=c.titleno and e.deptno=d.deptno";
		
		PreparedStatement pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		
		while(rs.next()){
			hashList.put(rs.getString("empname")+"("+rs.getString("dept")+"-"+rs.getString("title")+")", rs.getInt("empno"));
		}
		hashList.put("", 0);		
		
		String[] temps = new String[temp.size()];
		temp.toArray(temps);
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return hashList;
	}
	
	public HashMap<String, Integer> selectedDeptList() throws SQLException{
		HashMap<String, Integer> hashList = new HashMap<String, Integer>();
		
		String sql = "select deptno, deptname from department";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		
		while(rs.next()){
			hashList.put(rs.getString("deptname"), rs.getInt("deptno"));
		}
		hashList.put("", 0);
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return hashList;
	}*/
	


/*
//매니저 이름으로 번호를 - 수정용2
public ArrayList<Employee> updateItemByManager(Employee item) throws SQLException {
	ArrayList<Employee> emp = new ArrayList<>();
	
	String sql = "select empno from employee where empname =?";
	PreparedStatement pstmt = connection.prepareStatement(sql);
	pstmt.setString(1, item.getEmpName());
	ResultSet rs= pstmt.executeQuery();
	
	while (rs.next()) {
		emp.add(new Employee(rs.getInt(1)));
	}
	
	while (rs.next()) {
		empManager.add(rs.getString("empname"));
	}
	
	
	Integer[] arrManagerNo = new Integer[emp.size()];
	emp.toArray(arrManagerNo); //리스트를 배열로는 함수
	
//	System.out.println(empManager);
	
	DbUtil.close(rs);
	DbUtil.close(pstmt);
	
	return emp; //emp에는 지금 사원번호있음
}
*/

/*
//상급자명 콤보상자 : 번호로 이름
	public String[] selectedEmpNameByEmpNo(Employee item) throws SQLException {
		
		ArrayList<String> empManager = new ArrayList<>();
		PreparedStatement pstmt=null;
		if (item==null){
			String sql = "select empname from employee";
			pstmt = connection.prepareStatement(sql);
		}else{ //번호를 이름으로
			String sql = "select empname from employee where empno = ?";
			pstmt = connection.prepareStatement(sql);
			pstmt.setInt(1, item.getManager());
		}
		
		ResultSet rs = pstmt.executeQuery();
		
		while (rs.next()) {
			empManager.add(rs.getString("empname"));
		}
		
		String[] arrManager = new String[empManager.size()];
		empManager.toArray(arrManager); //리스트를 배열로는 함수
		
//		System.out.println(empManager);
		
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		
		return arrManager;
	}
	
*/
